package ca.ubc.cs304.library.actions;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;

import ca.ubc.cs304.library.sql.OracleDatabaseWrapper;

// Serge: I'll put my parts into this file
/*
 * So, what exactly do we have to do here? From the project:
 * 1) (DONE) Search for books using keyword search on titles, authors and subjects. 
 * 	  - The result is a list of books that match the search together with the number of copies that are in and out.
 * 	  - Need copies in and copies out... interesting.. :-)
 * 2) (DONE) Check his/her account. 
 *    - The system will display the items the borrower has currently borrowed and not yet returned, 
 *     any outstanding fines and the hold requests that have been placed by the borrower.
 * 3) Place a hold request for a book that is out. 
 *    - When the item is returned, the system sends an email to the borrower and informs the library 
 * 	    clerk to keep the book out of the shelves.
 * 4) (DONE) Pay a fine.
 */


public class BorrowerActions {
	/**
	 * Returns the status of the borrower - 3 queries
	 */
	public static class Status {
		/**
		 * Columns: title, issuedDate, amount
		 */
		public ResultSet fines;
		public ResultSet holds;
		public ResultSet booksOut;
		
	
	}
	
	/**
	 * Checks the validity of an account
	 * @param db OracleDatabaseWrapper (Already connected)
	 * @param sinOrStNo the sin or Student number of the borrower
	 * @param password the password of this borrower
	 * @throws Exception if no user with this login was found.
	 */
	private static void checkAccountValidity(OracleDatabaseWrapper db, int sinOrStNo, String password) throws Exception {
		// check the validity of this login
		PreparedStatement pStatement = db.createPreparedStatement(
				"SELECT bid FROM Borrower " +
				"WHERE sinOrStNo=(?) AND password=(?)");
		pStatement.setInt(1, sinOrStNo);
		pStatement.setString(2, password);
		pStatement.execute();
		
		// No matches?! What?!? - hehe This means more no user :-)
		if (!pStatement.getResultSet().next()) {
			throw new Exception("A user with that username and password was not found.");
		}
	}
	
	/**
	 * Wrap the parameters in % % wildcard's for an SQL statement 
	 * @param stringToWrap the thing t
	 * @return the 'wrapped' statement
	 */
	private static String wildCardWrap(String stringToWrap) {
		return "%" + stringToWrap + "%";
	}
	

	/**
	 * A versatile search method. Returns a list of books, optionally searched by the
	 * subject, title, or authors, and a combination of these.
	 * When more than one parameter is specified, the given search criteria
	 * are AND'ed together.
	 * No null value inputs, please :-)
	 * @param db OracleDatabaseWrapper (Already connected)
	 * @param partialSubject - blank, or some characters of the subject.
	 * @param partialAuthor - blank, or some characters of the author.
	 * @param partialTitle - blank, or some characters of the title.
	 * @return the ResultSet with the books found on the system.
	 * @throws Exception in case of any errors.
	 */
	public static ResultSet searchBooks(
			OracleDatabaseWrapper db, 	String partialSubject,
			String partialAuthor, 		String partialTitle) 
		throws Exception {
		
		connectionCheck(db);
		PreparedStatement statement = db.createPreparedStatement(
			"WITH BooksFound AS ( " + 
		    "  SELECT Book.callNumber FROM Book LEFT OUTER JOIN " +
		    "   HasAuthor ON Book.callNumber=HasAuthor.callNumber" +
		    "   WHERE (LOWER(name) LIKE LOWER(?) OR LOWER(mainAuthor) LIKE LOWER(?)) " +
		    " INTERSECT " +  // I have to INTERCECT here since oracle won't let me OUTER JOIN 3 tables :-/
		    "  SELECT Book.callNumber from Book LEFT OUTER JOIN " +
		    "   HasSubject ON Book.callNumber=HasSubject.callNumber " +
		    "   WHERE (LOWER(subject) LIKE LOWER(?))) " +
		    "SELECT BooksFound.callNumber as callNumber, title, mainAuthor, " +  
		    " sum(CASE WHEN LOWER(status)='in' THEN 1 ELSE 0 END) as copiesIn, count(copyNo) as copiesTotal " +
		    " FROM BooksFound, Book, BookCopy " +
		    " WHERE BooksFound.callNumber=BookCopy.callNumber AND Book.callNumber=BookCopy.callNumber " +
		    " AND (LOWER(Book.title) LIKE LOWER(?)) " +
		    " GROUP BY BooksFound.callNumber, title, mainAuthor");
		
		// Wrap the parameters in % % wildcards 
		statement.setString(1, wildCardWrap(partialAuthor));
		statement.setString(2, wildCardWrap(partialAuthor));
		statement.setString(3, wildCardWrap(partialSubject));
		statement.setString(4, wildCardWrap(partialTitle));
		
		return statement.executeQuery();
	}

	
	/**
	 * Run before each query to ensure oracle is connected :-)
	 * @param db the OracleDatabaseWrapper (connected or otherwise).
	 * @throws Exception in case oracle is not connected to.
	 */
	private static void connectionCheck (OracleDatabaseWrapper db) throws Exception {
		if (db == null || !db.isConnected()) 
			throw new Exception ("Not connected to Database");
	}

	/**
	 * Get the 'complete' status of a borrower
	 * @param db OracleDatabaseWrapper (Already connected)
	 * @param sinOrStNo the sin or Student number of the borrower
	 * @param password the password of this borrower
	 * @return BorrowerActions.Status object containing 3 result sets: fines, holds, booksOut.
	 * @throws Exception in case of errors
	 */
	public static  Status checkStatus(OracleDatabaseWrapper db, int sinOrStNo, String password) throws Exception {
		connectionCheck(db);
		
		// Check the login. (We do this later on in the query as well. 
		//  but this part give us a clearer error message :-)
		checkAccountValidity(db, sinOrStNo, password);
		
		// Now for the check status query - oh, I mean 3 queries :-)
		Status result = new Status();
		Statement statement = db.createStatement();
		result.fines = statement.executeQuery(
				"SELECT fid, title, TO_CHAR(issuedDate) as issued, amount, " +
				"CASE WHEN paidDate IS NULL THEN 'Not Paid' ELSE TO_CHAR(paidDate) END AS paid FROM( " +
				"  SELECT fid, title, issuedDate, amount, paidDate FROM Fine,Borrowing,Book,Borrower " +
				"  WHERE  Fine.borid=Borrowing.borid AND Borrowing.callNumber=Book.callNumber " +
				"  AND Borrowing.bid=Borrower.bid AND sinOrStNo=" + sinOrStNo + " AND password='" + password + 
				"')");
		
		statement = db.createStatement();
		result.holds = statement.executeQuery(
				"SELECT title, issuedDate, " + 
				"CASE WHEN issuedDate IS NULL THEN 'Not Yet' ELSE TO_CHAR(issuedDate) END AS filledOn FROM( " +
				"  SELECT title, issuedDate FROM HoldRequest, Borrower, Book " +
				"  WHERE HoldRequest.bid=Borrower.bid AND HoldRequest.callNumber=Book.callNumber" +
				"  AND Borrower.sinOrStNo=" + sinOrStNo + " AND password='" + password + "' " +
				")");
		
		statement = db.createStatement();
		result.booksOut = statement.executeQuery(
				"SELECT title, TO_CHAR(outdate) as out, TO_CHAR(dueDate) as due, "+
				"CASE WHEN dueDate <= sysdate THEN 'Over Due' ELSE 'Still Okay' END AS overDue FROM( " +
				"  SELECT title, outdate, bookTimeLimit + outdate as dueDate FROM( " +
				"    SELECT title, outdate, bookTimeLimit FROM Borrowing,Book,Borrower,BorrowerType " +
				"    WHERE Borrowing.callNumber=Book.callnumber AND Borrower.bid=Borrowing.bid " +
				"    AND Borrower.type=BorrowerType.type " +
				"    AND indate is NULL AND sinOrStNo=" + sinOrStNo + " AND password='" + password + "' " +
				"  )" +
				")");


		return result;
	}
	
	/**
	 * Makes a hold on an item. Checks that the item can't be checked out already.
	 * @param db OracleDatabaseWrapper (Already connected)
	 * @param callNumber of the Book to place on hold
	 * @param sinOrStNo for the login
	 * @param password for the login
	 * @throws Exception on any kind of error, including copies-are-still-in.
	 */
	public static void makeHold(OracleDatabaseWrapper db, String callNumber, int sinOrStNo, String password) 
			throws Exception {
		
			// Check the login. (We do this later on in the query as well. 
			//  but this part give us a clearer error message :-)
			checkAccountValidity(db, sinOrStNo, password);
			
			// Now, make the make hold query
			connectionCheck(db);
			PreparedStatement statement = db.createPreparedStatement(
					"SELECT Book.callNumber, " +
					" sum(CASE WHEN lower(status)='in' THEN 1 ELSE 0 END) as copiesIn, count(copyNo) as copiesTotal " +
					"FROM Book, BookCopy " +
					" WHERE Book.callNumber=BookCopy.callNumber AND BookCopy.callNumber=(?) " +
					"GROUP BY Book.callNumber");
			
			// Set the call number
			statement.setString(1, callNumber);
			ResultSet results1 = statement.executeQuery();
			if (!results1.next()) {
				throw new Exception("No such call number: " + callNumber + "(" + results1.getRow() + " present row)"); 
			}
			int copiesTotal = results1.getInt("copiesTotal");
			int copiesIn = results1.getInt("copiesIn");
			
			if (copiesIn > 0) {
				throw new Exception("Can't place hold on " + callNumber + ": there's still " + copiesIn + 
						" of " + copiesTotal + " copies in the library!");
			}
			
			// Now, place the hold:
			statement = db.createPreparedStatement(
				"INSERT INTO HoldRequest VALUES (seqHid.nextval, ( " +
					"SELECT bid FROM Borrower " +
					"WHERE sinOrStNo=(?) AND PASSWORD=(?)), " +
					"(?), NULL)");
			
			statement.setInt(1, sinOrStNo);
			statement.setString(2, password);
			statement.setString(3, callNumber);
			statement.execute();
			if (statement.getUpdateCount() != 1) {
				throw new Exception("makeHold(): Did not update exatly one row in the table!");
			}
	}
	
	/**
	 * Mark a fine as paid today.
	 * @param db OracleDatabaseWrapper (already connected) 
	 * @param fineID - the fine ID to pay.
	 * @param fineID 
	 * @param param 
	 * @throws Exception in case of errors
	 */
	public static void payFine(OracleDatabaseWrapper db, int fineID,  int sinOrStNo, String password) throws Exception {
		connectionCheck(db);
		
		// check the login 
		checkAccountValidity(db, sinOrStNo, password);
		
		// Now, actually, pay the fine :-)
		Statement statement = db.createStatement();
		statement.execute("UPDATE Fine SET paidDate=sysdate WHERE fid=" + fineID);
	}
}
